package guest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

public class Guest {


	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String db_user = "hr";
	String db_pwd = "hr";

	Connection conn;
	GuestVo vo;
	
	public Guest() {
		try {

			// 1)드라이버 로딩
			Class.forName(driver).newInstance();

			// 2)Connection 생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);
		} catch (Exception ex) {
			ex.getStackTrace();
		}
	}

	public Vector<GuestVo> list() {
		Vector<GuestVo> v = new Vector<>();
		String sql = "select * from guestbook where mid like ? or subject like ? or content like ? order by serial desc";
		try {
			PreparedStatement ps= conn.prepareStatement(sql);
			ps.setString(1, "%"+ vo.getFind()+ "%");
			ps.setString(2, "%"+ vo.getFind()+ "%");
			ps.setString(3, "%"+ vo.getFind()+"%");

			ResultSet rs= ps.executeQuery();
			
			while(rs.next()){
				
				GuestVo gv=new GuestVo();
				
				gv.setSerial(rs.getInt("serial"));
				gv.setMid(rs.getString("mid"));
				gv.setSubject(rs.getString("subject"));
				gv.setContent(rs.getString("content"));
				gv.setMdate(rs.getDate("mdate"));
				
				v.add(gv);
			}
			
			
			rs.close();
			ps.close();
			conn.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return v;
	}
	public boolean insert(){
		boolean flag= false;
		String sql="insert into guestbook (serial, mid, subject, content, mdate) values (no_guestbook.nextval,?,?,?,sysdate)";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getMid());
			ps.setString(2, vo.getSubject());
			ps.setString(3, vo.getContent());

			int cnt= ps.executeUpdate();
			
			if(cnt>0) flag=true;
			
			ps.close();
			conn.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return flag;
		
	}
	public boolean modify(){
		boolean flag=false;
		String sql="update guestbook set subject=?,content=? where serial=?";
		try{
			PreparedStatement ps= conn.prepareStatement(sql);
			ps.setString(1, vo.getSubject());
			ps.setString(2, vo.getContent());
			ps.setInt(3, vo.getSerial());
			
			int cnt=ps.executeUpdate();
			if(cnt>0) flag=true;
			
			ps.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return flag;
		
	}
	
	public boolean del(){
		boolean flag=false;
		String sql="delete from guestbook where serial=?";
		try{
			PreparedStatement ps= conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			int cnt=ps.executeUpdate();
			if(cnt>0) flag=true;
			
			ps.close();
			conn.close();
		}catch(Exception ex){
			
			ex.printStackTrace();
		}
		
		
		return flag;
	}
	
	//시리얼 번호가져오는 쿼리
	public GuestVo view(){
		GuestVo v= new GuestVo();
		String sql="select * from guestbook where serial=?";
		try{
			PreparedStatement ps= conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			ResultSet rs= ps.executeQuery();
			if(rs.next()){
				v.setSerial(rs.getInt("serial"));
				v.setMid(rs.getString("mid"));
				v.setSubject(rs.getString("subject"));
				v.setContent(rs.getString("content"));
				v.setMdate(rs.getDate("mdate"));
			}
			
			rs.close();
			ps.close();
			conn.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}
		return v;
	}
	
	
	
	
	public GuestVo getVo() {
		return vo;
	}

	public void setVo(GuestVo vo) {
		this.vo = vo;
	}


}
